import oracledb
import os
import pandas as pd
from datetime import datetime
from openpyxl.styles import Font
import openpyxl
from openpyxl.worksheet.hyperlink import Hyperlink


def export_table_names_to_single_sheet(cursor, excel_file, sheet_name='Tables'):
    """
    将 cursor 查询结果中的表名去重后，写入 Excel 的单个 sheet 中。

    :param cursor: 已执行查询的 cursor，结果包含表名（第一列）
    :param excel_file: 输出的 Excel 文件路径
    :param sheet_name: Excel 中的 sheet 名称
    """
    table_names = list({row[0] for row in cursor if row[0]})  

    if not table_names:
        print("未查询到任何表名。")
        return

    table_names.sort()

    print(f"共查询到 {len(table_names)} 个唯一表名，正在写入 Excel 文件：{excel_file}")

    df = pd.DataFrame({
        "Table Name": table_names,
    })

    with pd.ExcelWriter(excel_file, engine='openpyxl') as writer:
        df.to_excel(writer, sheet_name=sheet_name, index=False)

    print(f"所有唯一表名已成功导出到: {os.path.abspath(excel_file)} 中的 '{sheet_name}' sheet")

def export_all_tables_to_excel(cursor_tables, cursor_data, excel_file: str, schema: str = None):
    try:
        cursor_tables.execute("""
        SELECT obj.OBJECT_NAME AS TABLE_NAME,
               COALESCE(c.COMMENTS, 'No comment') AS COMMENTS
        FROM ALL_OBJECTS obj
        LEFT JOIN ALL_TAB_COMMENTS c 
          ON obj.OWNER = c.OWNER 
         AND obj.OBJECT_NAME = c.TABLE_NAME
        WHERE obj.OWNER = UPPER(:schema)
          AND obj.OBJECT_TYPE = 'TABLE'
        ORDER BY obj.OBJECT_NAME
        """, schema=schema,)
        table_names = [row[0] for row in cursor_tables if row[0]]
        if not table_names:
            print(f" 在 schema '{schema}' 中未找到任何表。")
            return

        print(f" 共找到 {len(table_names)} 个表，准备追加为 sheet...")

    except Exception as e:
        print(f" 查询表名失败: {e}")
        return

    for table_name in table_names:
        sheet_name = table_name[:31]  
        for char in r'\/:*?[]':
            sheet_name = sheet_name.replace(char, '_')

        try:
            cursor_data.execute("""
            SELECT col.COLUMN_NAME,
                   col.DATA_TYPE,
                   col.DATA_LENGTH,
                   col.DATA_PRECISION,
                   col.DATA_SCALE,
                   col.NULLABLE,
                   col.COLUMN_ID,
                   com.COMMENTS
            FROM ALL_TAB_COLUMNS col
            LEFT JOIN ALL_COL_COMMENTS com
              ON col.OWNER = com.OWNER 
             AND col.TABLE_NAME = com.TABLE_NAME 
             AND col.COLUMN_NAME = com.COLUMN_NAME
            WHERE col.OWNER = UPPER(:schema)
              AND col.TABLE_NAME = :table_name
            ORDER BY col.COLUMN_ID
            """, schema=schema, table_name=table_name)
            columns = [desc[0] for desc in cursor_data.description]
            df = pd.DataFrame(cursor_data.fetchall(), columns=columns)

            with pd.ExcelWriter(excel_file, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
                df.to_excel(writer, sheet_name=sheet_name, index=False)

            print(f"表 '{table_name}' 已追加为 sheet: '{sheet_name}'")

        except Exception as e:
            error_df = pd.DataFrame({"Error": [f"数据读取失败: {str(e)}"]})
            with pd.ExcelWriter(excel_file, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
                error_df.to_excel(writer, sheet_name=sheet_name, index=False)
            print(f"表 '{table_name}' 导出失败: {e}")

    print(f"所有表已追加写入 Excel 文件（保留原有内容）: {os.path.abspath(excel_file)}")
    print()

def add_internal_hyperlinks_to_first_sheet(excel_file: str, link_column: int = 1):
    wb = openpyxl.load_workbook(excel_file)
    
    first_sheet = wb.active if wb.active.title == wb.sheetnames[0] else wb[wb.sheetnames[0]]
    
    for row in first_sheet.iter_rows(min_row=1, max_col=link_column, max_row=first_sheet.max_row):
        cell = row[0]  # 第一列的单元格
        cell_value = cell.value
        if cell_value in wb.sheetnames:
            hyperlink = Hyperlink(ref=cell.coordinate, location=f"'{cell.value}'!A1", tooltip=cell_value)
            cell.hyperlink = hyperlink
            cell.font = Font(underline="single", color="0000FF") 
    
    wb.save(excel_file)

username = "MOC"
password = "1qaz@WSX"
host = "128.168.11.130"       
port = 1521
service_name = "cludb"    
schema_name = "MOC"   

output_excel = f"{schema_name}_tables_info_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"

try:
    dsn = f"{host}:{port}/{service_name}"
    oracledb.init_oracle_client(mode=oracledb.CLIENT_THICK)
    connection = oracledb.connect(user=username, password=password, dsn=dsn)
    print(f"成功连接到 Oracle 数据库，模式: {schema_name}")

    with (
        connection.cursor() as cursor_tables, 
        connection.cursor() as cursor_columns, 
        connection.cursor()as cursor_thirds
        ):

        tables_query = """
        SELECT obj.OBJECT_NAME AS TABLE_NAME,
               COALESCE(c.COMMENTS, 'No comment') AS COMMENTS
        FROM ALL_OBJECTS obj
        LEFT JOIN ALL_TAB_COMMENTS c 
          ON obj.OWNER = c.OWNER 
         AND obj.OBJECT_NAME = c.TABLE_NAME
        WHERE obj.OWNER = UPPER(:schema)
          AND obj.OBJECT_TYPE = 'TABLE'
        ORDER BY obj.OBJECT_NAME
        """
        cursor_thirds.execute(tables_query, schema=schema_name)
        export_table_names_to_single_sheet(cursor_thirds, output_excel)
        export_all_tables_to_excel(cursor_tables,cursor_columns, output_excel,schema_name)
        add_internal_hyperlinks_to_first_sheet(output_excel)
        
except oracledb.Error as e:
    print(f"Oracle 错误: {e}")
except Exception as e:
    print(f"其他错误: {e}")
finally:
    if 'connection' in locals() and connection:
        connection.close()
        print("数据库连接已关闭")

